﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Reflection;
using System.Data;
using System.Data.SqlClient;
using JDML.StaticExtensions;
using JDML.BindingList;
using JDML.FilterCondition;

namespace JDML.MappingClass
{
    public class TableMapping : BaseMapping
    {
        public string TableName { get; set; }
        public bool ReloadAfterInsert { get; set; }
        public bool ReloadAfterUpdate { get; set; }
        public TableMapping()
        {
        }
        public TableMapping(string tableName, List<FieldMapping> dbFields = null)
        {
            TableName = tableName;
            if (dbFields != null)
            {
                DBFields = dbFields;
            }
        }

        private DataSet findDataSet(Object o)
        {
            return findDataSet(o, null);
        }

        public FieldCondition getFieldCondition(String fieldName, Conditions conditions)
        {
            if (conditions != null)
            {
                if (conditions.ContainsKey(fieldName))
                {
                    return conditions[fieldName];
                }
            }
            return null;
        }

        private string getLogicSymbol(FieldCondition condition)
        {
            if (condition != null)
            {
                return LogicTypeConverter.ToSymbol(condition.LogicType);

            }
            return "And";
        }

        private string getConditionSymbol(FieldCondition condition)
        {
            if (condition != null)
            {
                return ConditionTypeConverter.ToSymbol(condition.Type);
            }
            return "=";
        }

        private DataSet findDataSet(Object o, Conditions conditions)
        {
            List<string> fieldNames = new List<string>();
            foreach (FieldMapping fieldmapping in DBFields)
            {
                if (fieldmapping.IsMapping)
                {
                    fieldNames.Add(fieldmapping.DBFieldName);
                }
            }
            string fields = string.Join(",", fieldNames.ToArray());
            StringBuilder sqlsb = new StringBuilder();
            sqlsb.Append("select ").Append(fields).Append(" from ").Append("[").Append(TableName).Append("]")
                .Append(" where").Append(" 1=1");
            SqlCommand cmd = new SqlCommand();
            foreach (FieldMapping fieldmapping in DBFields)
            {
                object value = fieldmapping.GetValue(o);
                if ((fieldmapping.IsMapping) && (value != null))
                {
                    FieldCondition condition = getFieldCondition(fieldmapping.FieldName, conditions);
                    string parameterName = "@" + fieldmapping.FieldName;
                    sqlsb.Append(" ");
                    sqlsb.Append(getLogicSymbol(condition));
                    sqlsb.Append(" ");
                    sqlsb.Append(fieldmapping.DBFieldName);
                    sqlsb.Append(" ");
                    //sqlsb.Append("=");
                    sqlsb.Append(getConditionSymbol(condition));
                    sqlsb.Append(" ");
                    sqlsb.Append(parameterName);
                    if (condition != null)
                    {
                        if ((condition.Type == ConditionType.Like) || (condition.Type == ConditionType.NotLike))
                        {
                            if (value is String)
                            {
                                value = "%" + (string)value + "%";
                            }
                        }
                    }
                    cmd.Parameters.AddWithValue(parameterName, value);
                }
            }
            cmd.CommandText = sqlsb.ToString();
            cmd.Connection = DAOExtension.Connection;
            cmd.Transaction = DAOExtension.Transaction;
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            da.Fill(ds);
            return ds;
        }

        public bool Load(Object o)
        {
            FieldMapping primaryfield = PrimaryKeyField;
            if (primaryfield == null)
            {
                throw new Exception("没有定义任何主键！");
            }
            List<string> fieldNames = new List<string>();
            foreach (FieldMapping fieldmapping in DBFields)
            {
                if (fieldmapping.IsMapping)
                {
                    fieldNames.Add(fieldmapping.DBFieldName);
                }
            }
            string fields = string.Join(",", fieldNames.ToArray());
            StringBuilder sqlsb = new StringBuilder();
            sqlsb.Append("select ").Append(fields).Append(" from ").Append("[").Append(TableName).Append("]")
                .Append(" where ");
            SqlCommand cmd = new SqlCommand();
            object value = primaryfield.GetValue(o);
            if (value == null)
            {
                throw new Exception("主键 " +primaryfield.FieldName + " 的值不能为null！");
            }
            else
            {
                string parameterName = "@" + primaryfield.FieldName;
                sqlsb.Append(" ");
                sqlsb.Append(primaryfield.DBFieldName);
                sqlsb.Append("=");
                sqlsb.Append(parameterName);
                cmd.Parameters.AddWithValue(parameterName, value);
            }

            cmd.CommandText = sqlsb.ToString();
            cmd.Connection = DAOExtension.Connection;
            cmd.Transaction = DAOExtension.Transaction;
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            da.Fill(ds);
            if (ds.Tables[0].Rows.Count == 1)
            {
                foreach (FieldMapping fieldmapping in DBFields)
                {
                    if (fieldmapping.IsMapping && (ds.Tables[0].Columns.Contains(fieldmapping.DBFieldName)))
                    {
                        Object fieldvalue = ds.Tables[0].Rows[0][fieldmapping.DBFieldName];
                        object returnvalue = null;
                        if (!Convert.IsDBNull(fieldvalue))
                        {
                            returnvalue = Convert.ChangeType(fieldvalue, fieldmapping.FieldType);
                        }
                        fieldmapping.SetValue(o, returnvalue);
                    }
                }
                return true;
            }
            return false;  
        }

        public bool Find(Object o)
        {
            return Find(o, null);
        }

        public bool Find(Object o, Conditions conditions)
        {
            DataSet ds = findDataSet(o, conditions);
            if (ds.Tables[0].Rows.Count > 0)
            {
                foreach (FieldMapping fieldmapping in DBFields)
                {
                    if (fieldmapping.IsMapping && (ds.Tables[0].Columns.Contains(fieldmapping.DBFieldName)))
                    {
                        Object fieldvalue = ds.Tables[0].Rows[0][fieldmapping.DBFieldName];
                        object value = null;
                        if (!Convert.IsDBNull(fieldvalue))
                        {
                            value = Convert.ChangeType(fieldvalue, fieldmapping.FieldType);
                        }
                        fieldmapping.SetValue(o, value);
                    }
                }
                return true;
            }
            return false;   
        }

        public IList<T> FindList<T>(Object o)
        {
            return FindList<T>(o, null);
        }

        public IList<T> FindList<T>(Object o, Conditions conditions)
        {
            SortedBindingList<T> list = new SortedBindingList<T>();
            DataSet ds = findDataSet(o, conditions);
            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
            {
                T newObject = (T)Activator.CreateInstance(o.GetType());
                foreach (FieldMapping fieldmapping in DBFields)
                {
                    if (fieldmapping.IsMapping && (ds.Tables[0].Columns.Contains(fieldmapping.DBFieldName)))
                    {
                        Object fieldvalue = ds.Tables[0].Rows[i][fieldmapping.DBFieldName];
                        object value = null;
                        if (!Convert.IsDBNull(fieldvalue))
                        {
                            value = Convert.ChangeType(fieldvalue, fieldmapping.FieldType);
                        }
                        fieldmapping.SetValue(newObject, value);
                    }
                }
                list.Add(newObject);
            }
            return list;
        }

        public bool Insert(Object o)
        {
            List<string> fieldNames = new List<string>();
            List<string> fieldValues = new List<string>();
            SqlCommand cmd = new SqlCommand();
            foreach(FieldMapping fieldmapping in DBFields)
            {
                object value = fieldmapping.GetValue(o);
                if ((fieldmapping.IsMapping) && (value != null))
                {
                    string parameterName = "@" + fieldmapping.FieldName;
                    fieldNames.Add(fieldmapping.DBFieldName);
                    fieldValues.Add(parameterName);
                    cmd.Parameters.AddWithValue(parameterName, value);
                }
            }
            string fields = string.Join(",", fieldNames.ToArray());
            string values = string.Join(",", fieldValues.ToArray());
            StringBuilder sqlsb = new StringBuilder();
            sqlsb.Append("insert into ").Append(TableName).Append("(").Append(fields).Append(")")
                .Append(" values").Append("(").Append(values).Append(") ");//.Append("select SCOPE_IDENTITY() as PrimaryKey");
            cmd.CommandText = sqlsb.ToString();
            cmd.Connection = DAOExtension.Connection;
            cmd.Transaction = DAOExtension.Transaction;
            //object oo = cmd.ExecuteScalar();
            //Console.WriteLine(oo);
            bool insertreturn = false;
            if (AutoGeneratePrimaryKeyField != null)
            {
                cmd.CommandText += "; SELECT SCOPE_IDENTITY() ";
                object keyvalue = cmd.ExecuteScalar();
                object identity = null;
                if (!Convert.IsDBNull(keyvalue))
                {
                    identity = Convert.ChangeType(keyvalue, AutoGeneratePrimaryKeyField.FieldType);
                    AutoGeneratePrimaryKeyField.SetValue(o, identity);
                    insertreturn = true;
                }
                else
                {
                    insertreturn = false;
                }
            }
            else
            {
                if (cmd.ExecuteNonQuery() > 0)
                {
                    insertreturn = true;
                }
                else
                {
                    insertreturn = false;
                }
            }
            if (insertreturn && ReloadAfterInsert)
            {
                o.Load();
            }
            return insertreturn;
        }

        public bool Update(Object o)
        {
            List<string> fieldUpdates = new List<string>();
            List<string> fieldConditions = new List<string>();
            SqlCommand cmd = new SqlCommand();
            foreach (FieldMapping fieldmapping in DBFields)
            {
                object value = fieldmapping.GetValue(o);
                if (fieldmapping.IsMapping && (value!=null))
                {
                    string parameterName = "@" + fieldmapping.FieldName;
                    if (!fieldmapping.DBAutoGenerate)
                    {
                        fieldUpdates.Add(fieldmapping.DBFieldName + "=" + parameterName); 
                    }
                    if (fieldmapping.DBPrimaryKey)
                    {
                        fieldConditions.Add(fieldmapping.DBFieldName + "=" + parameterName);
                    }
                    cmd.Parameters.AddWithValue(parameterName, value);
                }
            }
            string fields = string.Join(",", fieldUpdates.ToArray());
            string conditions = string.Join(" and ", fieldConditions.ToArray());
            StringBuilder sqlsb = new StringBuilder();
            sqlsb.Append("update ").Append("[").Append(TableName).Append("]").Append(" set ").Append(fields).Append(" where ")
                .Append(conditions);
            cmd.CommandText = sqlsb.ToString();
            cmd.Connection = DAOExtension.Connection;
            cmd.Transaction = DAOExtension.Transaction;
            if (cmd.ExecuteNonQuery() > 0)
            {
                if (ReloadAfterUpdate)
                {
                    o.Load();
                }
                return true;
            }
            return false;
        }

        public bool Delete(Object o)
        {
            List<string> fieldConditions = new List<string>();
            SqlCommand cmd = new SqlCommand();
            foreach (FieldMapping fieldmapping in DBFields)
            {
                object value = fieldmapping.GetValue(o);
                if (fieldmapping.IsMapping && (value != null))
                {
                    string parameterName = "@" + fieldmapping.FieldName;
                    if (fieldmapping.DBPrimaryKey)
                    {
                        fieldConditions.Add(fieldmapping.DBFieldName + "=" + parameterName);
                    }
                    cmd.Parameters.AddWithValue(parameterName, value);
                }
            }
            string conditions = string.Join(" and ", fieldConditions.ToArray());
            StringBuilder sqlsb = new StringBuilder();
            sqlsb.Append("delete ").Append(" from ").Append("[").Append(TableName).Append("]").Append(" where ")
                .Append(conditions);
            cmd.CommandText = sqlsb.ToString();
            cmd.Connection = DAOExtension.Connection;
            cmd.Transaction = DAOExtension.Transaction;
            if (cmd.ExecuteNonQuery() > 0)
            {
                return true;
            }
            return false;
        }
        public bool CreateTable(Object o)
        {
            List<string> fields = new List<string>();
            foreach (FieldMapping fieldmapping in DBFields)
            {
                if (fieldmapping.IsMapping)
                {
                    string fieldtype = string.Empty;
                    switch (fieldmapping.DbFieldType)
                    {
                        case DbType.String:
                            fieldtype = "varchar(max)";
                            break;
                        case DbType.AnsiString:
                            fieldtype = "varchar(max)";
                            break;
                        case DbType.Int32:
                            fieldtype = "int";
                            break;
                        case DbType.Byte:
                            fieldtype = "tinyint";
                            break;
                        case DbType.Int64:
                            fieldtype = "bigint";
                            break;
                        case DbType.Int16:
                            fieldtype = "smallint";
                            break;
                        case DbType.Boolean:
                            fieldtype = "bit";
                            break;
                        case DbType.Decimal:
                        case DbType.VarNumeric:
                            fieldtype = "decimal(18,4)";
                            break;
                        case DbType.Double:
                            fieldtype = "float";
                            break;
                        case DbType.Currency:
                            fieldtype = "money";
                            break;
                        case DbType.Binary:
                            fieldtype = "varbinary(max)";
                            break;
                        case DbType.Date:
                        case DbType.DateTime:
                        case DbType.DateTime2:
                            fieldtype = "datetime";
                            break;
                        case DbType.Single:
                            fieldtype = "real";
                            break;
                        case DbType.Guid:
                            fieldtype = "uniqueidentifier";
                            break;
                    }
                    string field = fieldmapping.DBFieldName + " " + fieldtype;
                    if(fieldmapping.DBAutoGenerate)
                    {
                        switch(fieldmapping.DbFieldType)
                        {
                            case DbType.Int16:
                            case DbType.Int32:
                            case DbType.Int64:
                            case DbType.Byte:
                                field = field + " not null identity(1,1)";
                                break;
                            case DbType.Guid:
                                field = field + " not null DEFAULT NewID()";
                                break;
                            case DbType.Date:
                            case DbType.DateTime:
                            case DbType.DateTime2:
                                field = field + " not null DEFAULT GetDate()";
                                break;
                        }
                    }
                    if(fieldmapping.DBPrimaryKey)
                    {
                        field = field + " primary key";
                    }
                    fields.Add(field);
                }
            }
            string fieldstr = string.Join(", ", fields);
            string sqlstr = "Create Table ";
            sqlstr += TableName;
            sqlstr += "(" + fieldstr + ")";
            SqlCommand cmd = new SqlCommand(sqlstr, DAOExtension.Connection);
            cmd.Transaction = DAOExtension.Transaction;
            if (cmd.ExecuteNonQuery() > 0)
            {
                return true;
            }
            return false;
        }
    }
}
